<?php
namespace app\controller;

use app\BaseController;
use think\facade\Db;
use think\Request;
use think\facade\View;
use think\helper\Str;
use think\exception\ValidateException;


// 课程评价统计
class EvaluationTotal extends BaseController
{
    public function short()
    {
        return View::fetch();
    }


    public function getShortList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();
        $where = [];

        if (isset($get['key'])) {
            if (!empty($get['key'])) {
                $where[] = ['b.name|b.userid', 'like', '%' . $get['key'] . '%'];
            }
        }

        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where[] = ['a.start_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        if (isset($get['type'])) {
            if ($get['type'] != '') {
                $where[] = ['a.type', '=', $get['type']];
            } else {
                $where[] = ['a.type', 'in', '2,3'];
            }
        } else {
            $where[] = ['a.type', 'in', '2,3'];
        }

        $where[] = ['a.is_review', '=', 0];

        $total = Db::table('o_subject_submit')
            ->alias('a')
            ->join('o_members b', 'a.userid=b.userid', 'left')
            ->where($where)
            ->count();

        $rows = Db::table('o_subject_submit')
            ->alias('a')
            ->join('o_members b', 'a.userid=b.userid', 'left')
            ->where($where)
            ->order('a.id', 'desc')
            ->field('a.id, a.aid, a.type, a.start_time, a.submit_time, b.department_name, b.division, b.name, a.userid')
            ->page($get['page'], $get['limit'])
            ->select()
            ->toArray();


        foreach ($rows as &$v) {

            $start_time = $v['start_time'];
            $v['start_time'] = date('Y-m-d H:i:s', $v['start_time']);
            $v['learning_time'] = $v['submit_time'] - $start_time;

            $userid = $v['userid'];

            $count = mb_strlen($v['userid']);
            if ($count == 6) {
                if (is_numeric($v['userid'])) {
                    $v['userid'] = '00'.$v['userid'];
                }
            }

            if ($v['type'] == 2) {
                $v['title'] = Db::name('learning')->where('id', '=', $v['aid'])->value('title');
            } else if ($v['type'] == 3) {
                $v['title'] = Db::name('live')->where('id', '=', $v['aid'])->value('title');
            }

            $letter = Db::name('subject_answer')
                ->alias('a')
                ->join('o_subject_option b', 'a.answer=b.id')
                ->where('a.userid', '=', $userid)
                ->where('a.aid', '=', $v['aid'])
                ->where('a.subject_id', '=', 2)
                ->value('b.letter');
            $v['fraction'] = $this->fraction($letter);
        }

        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }


    //+ 课程评价导出
    public function exportShort(Request $request)
    {

        if (!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        ob_end_clean();
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = 'Short Courses Survey评价列表' . date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['工号', '姓名', 'Division', '部门', '课程类型', '课程名称', '开始学习时间', '学习时长(s)', '分数']);


        $get = $request->get();
        $where = [];

        if (isset($get['key'])) {
            if (!empty($get['key'])) {
                $where[] = ['b.name|b.userid', 'like', '%' . $get['key'] . '%'];
            }
        }

        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where[] = ['a.start_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        if (isset($get['type'])) {
            if ($get['type'] != '') {
                $where[] = ['a.type', '=', $get['type']];
            } else {
                $where[] = ['a.type', 'in', '2,3'];
            }
        } else {
            $where[] = ['a.type', 'in', '2,3'];
        }

        $where[] = ['a.is_review', '=', 0];

        $total = Db::table('o_subject_submit')
            ->alias('a')
            ->join('o_members b', 'a.userid=b.userid', 'left')
            ->where($where)
            ->count();

        $nums = 10; //每次导出数量
        $step = ceil($total/$nums); //循环次数

        for($i = 0; $i < $step; $i++) {
            $start = $i * $nums;

            $rows = Db::table('o_subject_submit')
                ->alias('a')
                ->join('o_members b', 'a.userid=b.userid', 'left')
                ->where($where)
                ->order('a.id', 'desc')
                ->field('a.id, a.aid, a.type, a.start_time, a.submit_time, b.department_name, b.division, b.name, a.userid')
                ->limit($start, $nums)
                ->select()
                ->toArray();

            foreach ($rows as &$v) {

                $start_time = date('Y-m-d H:i:s', $v['start_time']);
                $learning_time = $v['submit_time'] - $v['start_time'];

                $count = mb_strlen($v['userid']);
                if ($count == 6) {
                    if (is_numeric($v['userid'])) {
                        $userid = "'00".$v['userid'];
                    } else {
                        $userid = $v['userid'];
                    }
                } else {
                    $userid = $v['userid'];
                }

                if ($v['type'] == 2) {
                    $title = Db::name('learning')->where('id', '=', $v['aid'])->value('title');
                    $type = 'E-Learning';
                } else if ($v['type'] == 3) {
                    $title = Db::name('live')->where('id', '=', $v['aid'])->value('title');
                    $type = '直播课';
                }

                $letter = Db::name('subject_answer')
                    ->alias('a')
                    ->join('o_subject_option b', 'a.answer=b.id')
                    ->where('a.userid', '=', $userid)
                    ->where('a.aid', '=', $v['aid'])
                    ->where('a.subject_id', '=', 2)
                    ->value('letter');
                $fraction = $this->fraction($letter);

                $arr = [
                    $userid,
                    $v['name'],
                    $v['division'],
                    $v['department_name'],
                    $type,
                    $title,
                    $start_time,
                    $learning_time,
                    $fraction
                ];

                fputcsv($fp, $arr);
            }
            flush();
        }
    }

    public function follow()
    {
        return View::fetch();
    }


    public function getFollowList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();
        $where = [];

        if (isset($get['key'])) {
            if (!empty($get['key'])) {
                $where[] = ['b.name|b.userid', 'like', '%' . $get['key'] . '%'];
            }
        }

        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where[] = ['a.start_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        if (isset($get['title'])) {
            if ($get['title'] != '') {
                $where[] = ['c.title', 'like', '%' . $get['title'] . '%'];
            }
        }

        $where[] = ['a.is_review', '=', 0];
        $where[] = ['a.type', '=', 1];

        $total = Db::table('o_subject_submit')
            ->alias('a')
            ->join('o_members b', 'a.userid=b.userid', 'left')
            ->join('o_classes c', 'a.aid=c.id', 'left')
            ->where($where)
            ->count();

        $rows = Db::table('o_subject_submit')
            ->alias('a')
            ->join('o_members b', 'a.userid=b.userid', 'left')
            ->join('o_classes c', 'a.aid=c.id', 'left')
            ->where($where)
            ->order('a.id', 'desc')
            ->field('a.id, a.aid, a.type, a.start_time, a.submit_time, b.department_name, b.division, b.name, a.userid, c.title')
            ->page($get['page'], $get['limit'])
            ->select()
            ->toArray();


        foreach ($rows as &$v) {

            $start_time = $v['start_time'];
            $v['start_time'] = date('Y-m-d H:i:s', $v['start_time']);
            $v['learning_time'] = $v['submit_time'] - $start_time;

            $userid = $v['userid'];

            $count = mb_strlen($v['userid']);
            if ($count == 6) {
                if (is_numeric($v['userid'])) {
                    $v['userid'] = '00'.$v['userid'];
                }
            }

            $letter = Db::name('subject_answer')
                ->alias('a')
                ->join('o_subject_option b', 'a.answer=b.id')
                ->where('a.userid', '=', $userid)
                ->where('a.aid', '=', $v['aid'])
                ->where('a.subject_id', '=', 6)
                ->value('letter');
            $v['fraction'] = $this->fraction($letter);
        }

        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }


    //+ 课程评价导出
    public function exportFollow(Request $request)
    {

        if (!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        ob_end_clean();
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = 'Follow-up Courses Survey评价列表' . date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['工号', '姓名', 'Division', '部门', '课程名称', '开始学习时间', '学习时长(s)', '分数']);


        $get = $request->get();
        $where = [];

        if (isset($get['key'])) {
            if (!empty($get['key'])) {
                $where[] = ['b.name|b.userid', 'like', '%' . $get['key'] . '%'];
            }
        }

        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where[] = ['a.start_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        if (isset($get['title'])) {
            if ($get['title'] != '') {
                $where[] = ['c.title', 'like', '%' . $get['title'] . '%'];
            }
        }

        $where[] = ['a.is_review', '=', 0];
        $where[] = ['a.type', '=', 1];

        $total = Db::table('o_subject_submit')
            ->alias('a')
            ->join('o_members b', 'a.userid=b.userid', 'left')
            ->join('o_classes c', 'a.aid=c.id', 'left')
            ->where($where)
            ->count();

        $nums = 10; //每次导出数量
        $step = ceil($total/$nums); //循环次数

        for($i = 0; $i < $step; $i++) {
            $start = $i * $nums;

            $rows = Db::table('o_subject_submit')
                ->alias('a')
                ->join('o_members b', 'a.userid=b.userid', 'left')
                ->join('o_classes c', 'a.aid=c.id', 'left')
                ->where($where)
                ->order('a.id', 'desc')
                ->field('a.id, a.aid, a.type, a.start_time, a.submit_time, b.department_name, b.division, b.name, a.userid, c.title')
                ->limit($start, $nums)
                ->select()
                ->toArray();

            foreach ($rows as &$v) {

                $start_time = date('Y-m-d H:i:s', $v['start_time']);
                $learning_time = $v['submit_time'] - $v['start_time'];

                $count = mb_strlen($v['userid']);
                if ($count == 6) {
                    if (is_numeric($v['userid'])) {
                        $userid = "'00".$v['userid'];
                    } else {
                        $userid = $v['userid'];
                    }
                } else {
                    $userid = $v['userid'];
                }


                $letter = Db::name('subject_answer')
                    ->alias('a')
                    ->join('o_subject_option b', 'a.answer=b.id')
                    ->where('a.userid', '=', $userid)
                    ->where('a.aid', '=', $v['aid'])
                    ->where('a.subject_id', '=', 6)
                    ->value('letter');
                $fraction = $this->fraction($letter);

                $arr = [
                    $userid,
                    $v['name'],
                    $v['division'],
                    $v['department_name'],
                    $v['title'],
                    $start_time,
                    $learning_time,
                    $fraction
                ];

                fputcsv($fp, $arr);
            }
            flush();
        }
    }

    public function initial()
    {
        return View::fetch();
    }

    public function getInitialList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();
        $where = [];

        if (isset($get['key'])) {
            if (!empty($get['key'])) {
                $where[] = ['b.name|b.userid', 'like', '%' . $get['key'] . '%'];
            }
        }

        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where[] = ['a.start_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        if (isset($get['title'])) {
            if ($get['title'] != '') {
                $where[] = ['c.title', 'like', '%' . $get['title'] . '%'];
            }
        }

        $where[] = ['a.is_review', '=', 1];
        $where[] = ['a.type', '=', 1];

        $total = Db::table('o_subject_submit')
            ->alias('a')
            ->join('o_members b', 'a.userid=b.userid', 'left')
            ->join('o_classes c', 'a.aid=c.id', 'left')
            ->where($where)
            ->count();

        $rows = Db::table('o_subject_submit')
            ->alias('a')
            ->join('o_members b', 'a.userid=b.userid', 'left')
            ->join('o_classes c', 'a.aid=c.id', 'left')
            ->where($where)
            ->order('a.id', 'desc')
            ->field('a.id, a.aid, a.type, a.start_time, a.submit_time, b.department_name, b.division, b.name, a.userid, c.title')
            ->page($get['page'], $get['limit'])
            ->select()
            ->toArray();


        foreach ($rows as &$v) {

            $start_time = $v['start_time'];
            $v['start_time'] = date('Y-m-d H:i:s', $v['start_time']);
            $v['learning_time'] = $v['submit_time'] - $start_time;

            $userid = $v['userid'];

            $count = mb_strlen($v['userid']);
            if ($count == 6) {
                if (is_numeric($v['userid'])) {
                    $v['userid'] = '00'.$v['userid'];
                }
            }

            $letter = Db::name('subject_answer')
                ->alias('a')
                ->join('o_subject_option b', 'a.answer=b.id')
                ->where('a.userid', '=', $userid)
                ->where('a.aid', '=', $v['aid'])
                ->where('a.subject_id', '=', 13)
                ->value('letter');
            $v['fraction'] = $this->fraction($letter);
        }

        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }


    //+ 课程评价导出
    public function exportInitial(Request $request)
    {

        if (!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        ob_end_clean();
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = 'Follow-up Courses Survey评价列表' . date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['工号', '姓名', 'Division', '部门', '课程名称', '开始学习时间', '学习时长(s)', '分数']);


        $get = $request->get();
        $where = [];

        if (isset($get['key'])) {
            if (!empty($get['key'])) {
                $where[] = ['b.name|b.userid', 'like', '%' . $get['key'] . '%'];
            }
        }

        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where[] = ['a.start_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        if (isset($get['title'])) {
            if ($get['title'] != '') {
                $where[] = ['c.title', 'like', '%' . $get['title'] . '%'];
            }
        }

        $where[] = ['a.is_review', '=', 1];
        $where[] = ['a.type', '=', 1];

        $total = Db::table('o_subject_submit')
            ->alias('a')
            ->join('o_members b', 'a.userid=b.userid', 'left')
            ->join('o_classes c', 'a.aid=c.id', 'left')
            ->where($where)
            ->count();

        $nums = 10; //每次导出数量
        $step = ceil($total/$nums); //循环次数

        for($i = 0; $i < $step; $i++) {
            $start = $i * $nums;

            $rows = Db::table('o_subject_submit')
                ->alias('a')
                ->join('o_members b', 'a.userid=b.userid', 'left')
                ->join('o_classes c', 'a.aid=c.id', 'left')
                ->where($where)
                ->order('a.id', 'desc')
                ->field('a.id, a.aid, a.type, a.start_time, a.submit_time, b.department_name, b.division, b.name, a.userid, c.title')
                ->limit($start, $nums)
                ->select()
                ->toArray();

            foreach ($rows as &$v) {

                $start_time = date('Y-m-d H:i:s', $v['start_time']);
                $learning_time = $v['submit_time'] - $v['start_time'];

                $count = mb_strlen($v['userid']);
                if ($count == 6) {
                    if (is_numeric($v['userid'])) {
                        $userid = "'00".$v['userid'];
                    } else {
                        $userid = $v['userid'];
                    }
                } else {
                    $userid = $v['userid'];
                }


                $letter = Db::name('subject_answer')
                    ->alias('a')
                    ->join('o_subject_option b', 'a.answer=b.id')
                    ->where('a.userid', '=', $userid)
                    ->where('a.aid', '=', $v['aid'])
                    ->where('a.subject_id', '=', 13)
                    ->value('letter');
                $fraction = $this->fraction($letter);

                $arr = [
                    $userid,
                    $v['name'],
                    $v['division'],
                    $v['department_name'],
                    $v['title'],
                    $start_time,
                    $learning_time,
                    $fraction
                ];

                fputcsv($fp, $arr);
            }
            flush();
        }
    }

    protected function fraction($key)
    {
        $data = ['A' => 0, 'B' => 1, 'C' => 2, 'D' => 3, 'E' => 4, 'F' => 5, 'G' => 6, 'H' => 7, 'I' => 8, 'J' => 9, 'K' => 10];
        return $data[$key];
    }
}